'''

Case Type： 表文件
Case Name： 表文件丢失
Create At:  2020/07/01
Owner    :  @wan005
Descption:  1、创建表，插入数据，加入索引，利用查询表，发现使用索引
2、修改索引文件权限，令数据库用户无法访问
3.使用索引查询数据
4、重启数据库
5. 使用索引查询数据
6.恢复权限
7.使用索引查询数据

'''
import os
import sys
import unittest
import _thread
import time
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import *
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common


class IndexFileDamaged(unittest.TestCase):
    sh = CommonSH()
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info('------------Opengauss_Reliability_Dbsys_Case065.py start------------')
        self.DN1_PATH = macro.DB_INSTANCE_PATH
        self.dbUserNode = Node(node='PrimaryDbUser')
        self.com = Common()
        self.Constant = Constant()
        self.t_name = 'reliability_dbsys065'
        self.rmpath = ''

    def test_del_indexfile(self):
        # 新建表
        self.log.info('----------------------------创建表，并写入数据-----------------------------')
        msg = self.com.createLargeNumTable(self.dbUserNode, self.t_name, 1, 100000)
        self.log.info(msg)

        # 建索引
        self.log.info('----------------------------建索引-----------------------------')
        sql_cmd = f"create unique index t_index on {self.t_name}(t_id);"
        self.log.info(sql_cmd)
        result = self.sh.executDbSql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.CREATE_INDEX_SUCCESS_MSG,result)

        # 写脏页避免影响启动
        sql_cmd = f"vacuum full"
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)

        # 查询oid
        self.log.info('----------------------------查询oid-----------------------------')
        sql_cmd = f"select oid from pg_class where relname='t_index';"
        self.log.info(sql_cmd)
        result = self.sh.executDbSql(sql_cmd)
        self.log.info(result)
        oid = result.splitlines()[-2]

        # 通过oid查询文件位置
        self.log.info('----------------------------通过oid查询文件位置-----------------------------')
        sql_cmd = f"select pg_relation_filepath({oid}::regclass);"
        self.log.info(sql_cmd)
        result = self.sh.executDbSql(sql_cmd)
        self.log.info(result)
        path = result.splitlines()[-2].strip()

        time.sleep(2)
        self.log.info('----------------------------修改索引文件权限-----------------------')
        self.rmpath = os.path.join(self.DN1_PATH, path)
        rm_cmd = 'chmod 000 ' + self.rmpath
        self.log.info(rm_cmd)
        msg = self.dbUserNode.sh(rm_cmd).result()
        self.log.info(msg)

        # 查询表数据，有提示信息
        self.log.info('-------------------使用索引 fail---------------------')
        sql_cmd = f"select count(*) from {self.t_name} where t_id > 10;"
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn(self.Constant.PERMISSION_DENIED, msg)

        time.sleep(180)

        self.log.info('----------------重启数据库----------------------')
        is_stop = self.sh.stopDbCluster()
        self.assertTrue(is_stop)
        # 启动集群，校验集群状态
        self.sh.startDbCluster()
        db_status = self.sh.getDbClusterStatus('detail')
        self.log.info(db_status)
        self.assertIn(self.Constant.PRIMARY_NORMAL, db_status)
        self.assertLessEqual(db_status.count(self.Constant.STANDBY_NORMAL), 2)

        self.log.info('-------------------使用索引 fail---------------------')
        sql_cmd = f"select count(*) from {self.t_name} where t_id > 10;"
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn(self.Constant.PERMISSION_DENIED, msg)

        # 修改回索引权限重启数据库
        self.log.info('--------------------------恢复权限-------------------------')
        rm_cmd = 'chmod 600 ' + self.rmpath
        msg = self.dbUserNode.sh(rm_cmd).result()
        self.log.info(msg)

        self.log.info('-------------------使用索引---------------------')
        sql_cmd = f"select count(*) from {self.t_name} where t_id > 10;"
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn('count', msg)

    def tearDown(self):
        self.log.info('-----------------------恢复权限----------------------')
        if self.rmpath != '':
            rm_cmd = 'chmod 600 ' + self.rmpath
            self.log.info(rm_cmd)
            msg = self.dbUserNode.sh(rm_cmd).result()
        # 清理环境，删除表
        self.log.info('------------------删除表-------------------------')
        msg = self.com.createLargeNumTable(self.dbUserNode, self.t_name, del_sign=True)
        self.log.info(msg)
        self.log.info('--------------Opengauss_Reliability_Dbsys_Case065 执行完成------------------------')
